﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using OfficeOpenXml;
using System.Drawing;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using Dapper;
using System.Data.SqlTypes;

namespace ExcelFileQueryTool
{
    public sealed class ClientFileService
    {
        static readonly string[] AddressColumnIndex = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J" };

        static readonly Regex RegexYearCell = new Regex(@"^(\d+)年(\d+月)?");

        static readonly Regex RegexNumber = new Regex(@"^\d+$");

        static readonly Dictionary<string, CRMPeople> PeopleCache = new Dictionary<string, CRMPeople>(50);

        public static readonly int DEFAULT_INVALID_CLIENT_ID = 0;

        public string ClientFile { get; private set; }

        public string ClientName { get; private set; }


        public ClientFileService(string file)
        {
            if (string.IsNullOrEmpty(file))
                throw new ArgumentNullException("file");

            if (!File.Exists(file))
                throw new FileNotFoundException("客户数据文件不存在", file);

            ClientFile = file;
            ClientName = GetClientNameFromFile(file);
        }

        /// <summary>
        /// 从客户文件获取联系信息
        /// </summary>
        /// <returns>返回数组，null为匹配失败。下标对应如下：0-联系人，1-联系方式，2-地址</returns>
        public static string[] RetriveContactInfoFromFile(string file)
        {
            using (ExcelPackage p = new ExcelPackage(new FileInfo(file)))
            {
                return RetriveContactInfo(p.Workbook.GetMainSheet().HeaderFooter.OddHeader.LeftAlignedText);
            }
        }

        /// <summary>
        /// 分解并获取联系信息
        /// </summary>
        /// <returns>返回数组，null为匹配失败。下标对应如下：0-联系人，1-联系方式，2-地址</returns>
        public static string[] RetriveContactInfo(string contactInfo)
        {
            if (string.IsNullOrEmpty(contactInfo))
                return null;

            string[] infoArr = null;
            var info_match = Regex.Match(contactInfo, AppConfig.ClientFileContactInfoRegexPattern);

            if (info_match.Success)
            {
                infoArr = new string[3];

                infoArr[0] = info_match.Groups[1].Value.Trim();
                infoArr[1] = info_match.Groups[2].Value.Trim();
                infoArr[2] = info_match.Groups[3].Value.Trim();
            }

            return infoArr;
        }

        public static string GetClientNameFromFile(string file)
        {
            if (string.IsNullOrEmpty(file))
                return null;

            var name = Path.GetFileNameWithoutExtension(file).Replace("[对数表]", string.Empty);
            return AppConfig.GetMappedName(name);
        }

        public static string GetClientTypeFromFile(string file)
        {
            if (string.IsNullOrEmpty(file))
                throw new ArgumentNullException("file");

            string ctype = new DirectoryInfo(Path.GetDirectoryName(file)).Name;
            if (ctype == "对数表")
                ctype = "其它";

            return ctype;
        }

        public static bool IsTotalRow(ExcelWorksheet sheet, int row)
        {
            if (sheet == null)
                return false;

            var cell = sheet.Cells[row, 1];
            if (cell == null)
                return false;

            if (!cell.Merge)
                return false;

            var val = cell.GetValue<string>();
            if (val == null)
                return false;

            return val.Contains("欠款")
                || val.Contains("合计")
                || val.Contains("总计")
                || val.Contains("小计")
                || val.Contains("货款");
        }

        private static int GetFirstEmptyRowIndex(ExcelWorksheet sourceSheet, int desiredRowsCount)
        {
            if (IsTotalRow(sourceSheet, sourceSheet.Dimension.Rows) == false)
                throw new Exception("缺少汇总行，无效的数据表：" + sourceSheet.Name);

            var lastRowCell = sourceSheet.Cells[sourceSheet.Dimension.Rows, 1];

            var lastEmptyRowIndex = lastRowCell.End.Row - 1;
            var rowIndex = lastEmptyRowIndex;
            var cell = sourceSheet.Cells[rowIndex, DataSheetColumnIndex.Product]; // 可能最后一行的日期列是合并单元格，所以用货物列作为判断依据可以保证准确性

            // no empty rows
            if (string.IsNullOrWhiteSpace(cell.GetValue<string>()) == false)
            {
                // insert a new empty row
                sourceSheet.InsertRow(lastRowCell.End.Row, 1, lastEmptyRowIndex);

                // update row index after insertion
                lastEmptyRowIndex++;
                rowIndex = lastEmptyRowIndex;

                // clear cells' value
                for (var i = 1; i <= 10; i++)
                    sourceSheet.Cells[rowIndex, i].Value = null;

                sourceSheet.Cells[rowIndex, DataSheetColumnIndex.LoadingFee].Value = 0m;
                sourceSheet.Cells[rowIndex, DataSheetColumnIndex.ShippingFee].Value = 0m;
                sourceSheet.Cells[rowIndex, DataSheetColumnIndex.Total].Formula = string.Format("=$C{0}*$E{0}+$F{0}+$G{0}", rowIndex);

                //todo: fix total formula
            }

            // at least there is a header row
            while (rowIndex > 1)
            {
                // 如果首行是合并列，就确定了当前行非空
                // 可能最后一行的日期列是合并单元格，所以用货物列作为判断依据可以保证准确性
                if (sourceSheet.Cells[rowIndex - 1, 1].Merge
                    || string.IsNullOrWhiteSpace(sourceSheet.Cells[rowIndex - 1, DataSheetColumnIndex.Product].GetValue<string>()) == false)
                    break;

                rowIndex--;
            }

            // insert rows for storing
            var emptyRowsCount = lastEmptyRowIndex - rowIndex;
            var emptyRowsNeedCount = desiredRowsCount - emptyRowsCount;

            if (emptyRowsNeedCount > 0)
            {
                // fix printarea
                if (sourceSheet.PrinterSettings.PrintArea != null)
                {
                    var rowDelta = sourceSheet.Dimension.End.Row + emptyRowsNeedCount - (sourceSheet.PrinterSettings.PrintArea.Start.Row - 1);
                    var colDelta = sourceSheet.PrinterSettings.PrintArea.End.Column;
                    sourceSheet.PrinterSettings.PrintArea = sourceSheet.PrinterSettings.PrintArea.Offset(0, 0, rowDelta, colDelta);
                }

                var lastEmptyRow = sourceSheet.Cells[string.Format("A{0}:J{0}", lastEmptyRowIndex)];

                sourceSheet.InsertRow(lastEmptyRowIndex, emptyRowsNeedCount, lastEmptyRowIndex);

                // manully fix new cells' style
                //for (int i = rowIndex; i < lastEmptyRowIndex; i++)
                //{
                //    for (int j = 1; j <= lastEmptyRow.Columns; j++)
                //    {
                //        var srcCell = sourceSheet.Cells[lastEmptyRowIndex, j];
                //        var targetCell = sourceSheet.Cells[i, j];
                //        targetCell.StyleID = srcCell.StyleID;
                //        targetCell.Style.Border = srcCell.Style.Border;
                //        targetCell.Style.Font = srcCell.Style.Font;
                //        targetCell.Style.XfId = srcCell.Style.XfId;
                //        targetCell.Style.Numberformat = srcCell.Style.Numberformat;
                //    }
                //}

                foreach (var colAddr in AddressColumnIndex)
                {
                    string address = string.Format("{0}{1}:{0}{2}"
                    , colAddr
                    , lastEmptyRowIndex
                    , (lastEmptyRowIndex + emptyRowsNeedCount - 1));

                    var range = sourceSheet.Cells[address];
                    range.Style.Border = lastEmptyRow.Style.Border;
                    range.Style.Font = lastEmptyRow.Style.Font;
                    range.Style.XfId = lastEmptyRow.Style.XfId;
                    range.Style.Numberformat = lastEmptyRow.Style.Numberformat;
                }

                #region fix out-reference

                foreach (var conclusionSheet in sourceSheet.Workbook.Worksheets.Where(x => ExcelWorkSheetUtillty.IsConclusionSheet(x)))
                {
                    string originalFormula;
                    string newFormula;

                    // 含有特殊字符或数字开头的表明需要单引号括起来
                    if (sourceSheet.DoesSheetRefNeedQuote())
                    {
                        originalFormula = "'" + sourceSheet.Name + "'!H" + lastRowCell.Start.Row;
                        newFormula = "'" + sourceSheet.Name + "'!H" + sourceSheet.Dimension.End.Row;
                    }
                    else
                    {
                        originalFormula = sourceSheet.Name + "!H" + lastRowCell.Start.Row;
                        newFormula = sourceSheet.Name + "!H" + sourceSheet.Dimension.End.Row;
                    }

                    for (int cRowIdx = 2; cRowIdx < conclusionSheet.Dimension.End.Row; cRowIdx++)
                    {
                        var refCell = conclusionSheet.Cells[cRowIdx, 3];
                        if (originalFormula.Equals(refCell.Formula, StringComparison.InvariantCultureIgnoreCase))
                        {
                            refCell.Formula = newFormula;
                        }
                        else
                        {
                            refCell = conclusionSheet.Cells[cRowIdx, 4];
                            if (originalFormula.Equals(refCell.Formula, StringComparison.InvariantCultureIgnoreCase))
                            {
                                refCell.Formula = newFormula;
                            }
                        }
                    }
                }

                #endregion
            }

            return rowIndex;
        }

        private void FillValues(BillSales sales_info, ExcelRange row)
        {
            // set style
            row[row.Start.Row, DataSheetColumnIndex.Date].Style.Numberformat.Format = "M-d";
            row[row.Start.Row, DataSheetColumnIndex.Date].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
            row[row.Start.Row, DataSheetColumnIndex.Date].Style.Font.Size = 12;
            row[row.Start.Row, DataSheetColumnIndex.Date].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
            row[row.Start.Row, DataSheetColumnIndex.Product].Style.Font.Size = 12;
            row[row.Start.Row, DataSheetColumnIndex.Product].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
            row[row.Start.Row, DataSheetColumnIndex.Amount].Style.Font.Size = 12;
            row[row.Start.Row, DataSheetColumnIndex.Amount].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
            row[row.Start.Row, DataSheetColumnIndex.Unit].Style.Font.Size = 12;
            row[row.Start.Row, DataSheetColumnIndex.Unit].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
            row[row.Start.Row, DataSheetColumnIndex.Price].Style.Font.Size = 12;
            row[row.Start.Row, DataSheetColumnIndex.Price].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
            row[row.Start.Row, DataSheetColumnIndex.LoadingFee].Style.Font.Size = 12;
            row[row.Start.Row, DataSheetColumnIndex.LoadingFee].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
            row[row.Start.Row, DataSheetColumnIndex.ShippingFee].Style.Font.Size = 12;
            row[row.Start.Row, DataSheetColumnIndex.ShippingFee].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
            row[row.Start.Row, DataSheetColumnIndex.Total].Style.Font.Size = 12;
            row[row.Start.Row, DataSheetColumnIndex.Total].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
            row[row.Start.Row, DataSheetColumnIndex.Note].Style.Font.Size = 12;
            row[row.Start.Row, DataSheetColumnIndex.Note].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
            row[row.Start.Row, DataSheetColumnIndex.BillNo].Style.Numberformat.Format = "@";
            row[row.Start.Row, DataSheetColumnIndex.BillNo].Style.Font.Size = 12;
            row[row.Start.Row, DataSheetColumnIndex.BillNo].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);

            row[row.Start.Row, DataSheetColumnIndex.Date].Value = sales_info.SalesDate;
            row[row.Start.Row, DataSheetColumnIndex.Product].Value = sales_info.ProductName;
            row[row.Start.Row, DataSheetColumnIndex.Amount].Value = sales_info.ProductAmount;
            row[row.Start.Row, DataSheetColumnIndex.Unit].Value = sales_info.ProductUnit;
            row[row.Start.Row, DataSheetColumnIndex.Price].Value = sales_info.ProductPrice;
            row[row.Start.Row, DataSheetColumnIndex.LoadingFee].Value = sales_info.LoadingFee;
            row[row.Start.Row, DataSheetColumnIndex.ShippingFee].Value = sales_info.ShippingFee;
            row[row.Start.Row, DataSheetColumnIndex.Total].Formula = string.Format("=$C{0}*$E{0}+$F{0}+$G{0}", row.Start.Row);
            row[row.Start.Row, DataSheetColumnIndex.BillNo].Value = sales_info.BillNumber;

            if (string.IsNullOrEmpty(sales_info.ShippingMethod))
                row[row.Start.Row, DataSheetColumnIndex.Note].Value = sales_info.Note;
            else if (string.IsNullOrEmpty(sales_info.Note))
                row[row.Start.Row, DataSheetColumnIndex.Note].Value = sales_info.ShippingMethod;
            else
                row[row.Start.Row, DataSheetColumnIndex.Note].Value = sales_info.ShippingMethod + "," + sales_info.Note;

            if (sales_info.BillType == (short)BillSalesType.Income || sales_info.BillType == (short)BillSalesType.Pay)
            {
                for (int i = 1; i <= 10; i++)
                {
                    row[row.Start.Row, i].Style.Font.Bold = true;
                    row[row.Start.Row, i].Style.Font.Color.SetColor(Color.Red);
                }
            }
        }

        public static int GetClientId(string clientName, CrmPeopleService svc)
        {
            CRMPeople people = null;

            if (PeopleCache.ContainsKey(clientName))
            {
                people = PeopleCache[clientName];
            }
            else
            {
                people = svc.GetByName(clientName);

                // 不能把null值存放进去，因为客户可能随时被创建
                if (people != null)
                    PeopleCache.Add(clientName, people);
            }

            return people == null ? DEFAULT_INVALID_CLIENT_ID : people.Id.Value;
        }

        /// <summary>
        /// 销售明细
        /// </summary>
        /// <param name="log"></param>
        /// <param name="svc"></param>
        /// <returns></returns>
        public static BillSales CreateDetailSalesItem(ExcelWorkLogItem log, CrmPeopleService svc)
        {
            if (log == null)
                return null;

            if (svc == null)
                throw new ArgumentNullException("svc");

            switch (log.类型)
            {
                case "销售":
                case "进货":
                    // 单价可不管，因为有些是赠送或抵数的记录
                    if (log.数量 == 0 || string.IsNullOrEmpty(log.单位))
                        throw new Exception("转换日记记录失败，[销售/进货]类型的记录必须录入数量/单位。");
                    break;

                case "收入":
                case "支出":
                    if (log.已收付货款 == 0 && (log.数量 == 0 || string.IsNullOrEmpty(log.单位) || log.单价 == 0))
                        throw new Exception("转换日记记录失败，[收入/支出]类型且无收付的记录必须录入数量/单价/单位。");
                    break;

                default:
                    break;
            }

            if (log.数量 == 0 && string.IsNullOrEmpty(log.单位))
                return null;

            BillSales bill = new BillSales();

            bill.ClientName = log.客户供应商;
            bill.ClientId = GetClientId(bill.ClientName, svc);
            bill.BillType = GetBillTypeByLogType(log.类型);
            bill.Note = log.备注;

            if (bill.BillType == (short)BillSalesType.ReturnForSale)
                bill.ProductName = "[退]" + log.货物;
            else
                bill.ProductName = log.货物;

            // 销售退货/进货的数量需要变为负数
            int amountDelta = 1;
            if (bill.BillType == (short)BillSalesType.ReturnForSale
                || bill.BillType == (short)BillSalesType.Purchase)
                amountDelta = -1;

            // 优惠/收入/损失的价格需要变为负数
            int priceDelta = 1;
            if (bill.BillType == (short)BillSalesType.Discount
                || bill.BillType == (short)BillSalesType.Income
                || bill.BillType == (short)BillSalesType.Lost)
                priceDelta = -1;

            // 支出且未付清的记录，价格需要变为负数
            if (bill.BillType == (short)BillSalesType.Pay && log.IsPaidOff == false)
                priceDelta = -1;

            bill.ProductAmount = log.数量 * amountDelta;
            bill.ProductPrice = log.单价 * priceDelta;

            /*note: 向别人进货，运费/卸车处理(暂时只能在入账时另外一行列出运费/卸车，因为数据中没有足够的判断依据)：
                    1.我支运卸，需要免除运费/卸车，即赋值为0
                    2.我支运，别人支卸，只需免除运费，卸车保留
                    3.我支卸，别人支运，只需免除卸车，运费保留
            */
            bill.ShippingFee = log.运装卸费 * amountDelta * priceDelta; // 运费/装卸费需要根据单价/数量的detal指标共同决定            

            bill.TotalMoney = log.货款合计 * amountDelta * priceDelta;
            bill.LoadingFee = 0;
            bill.CreatedTime = DateTime.Now;
            bill.IsPaidOff = log.IsPaidOff;
            bill.LastUpdatedTime = DateTime.Now;
            bill.ProductId = 0;
            bill.ProductUnit = log.单位;
            bill.SalesDate = log.日期;
            bill.ShippingAddress = log.备注;
            bill.ShippingMethod = log.运输方式;
            bill.Status = (short)DBRecordStatus.Normal;

            return bill;
        }

        /// <summary>
        /// 客户收/付款记录
        /// </summary>
        /// <param name="log"></param>
        /// <param name="svc"></param>
        /// <returns></returns>
        public static BillSales CreatePaidSalesItem(ExcelWorkLogItem log, CrmPeopleService svc)
        {
            if (log == null)
                return null;

            if (log.已收付货款 == 0)
                return null;

            if (string.IsNullOrEmpty(log.收付款人))
                throw new Exception("收款记录缺少收/付款人");

            if (svc == null)
                throw new ArgumentNullException("svc");

            BillSales item = new BillSales();
            var price_delta = 1;

            switch (log.类型)
            {
                case "进货":
                case "退货":
                case "支出":
                    item.BillType = GetBillTypeByLogType("支出");
                    break;

                case "收入":
                case "销售":
                    item.BillType = GetBillTypeByLogType("收入");
                    price_delta = -1;
                    break;

                default:
                    return null;
            }

            item.ClientName = log.客户供应商;
            item.ClientId = GetClientId(item.ClientName, svc);

            if (string.IsNullOrEmpty(log.货物))
                item.ProductName = log.类型 + "款项";
            else
                item.ProductName = log.货物;

            if (string.IsNullOrEmpty(log.备注))
                item.Note = log.收付款人 + "经手";
            else
                item.Note = log.备注 + "," + log.收付款人 + "经手";

            item.SalesDate = log.日期;
            item.ProductUnit = "宗";
            item.ProductAmount = 1;
            item.ProductPrice = log.已收付货款 * price_delta;
            item.Status = (short)DBRecordStatus.Normal;
            item.CreatedTime = DateTime.Now;
            item.LastUpdatedTime = DateTime.Now;
            item.TotalMoney = item.ProductAmount * item.ProductPrice;
            item.IsPaidOff = log.IsPaidOff;

            return item;
        }


        /// <summary>
        /// 代收/付记录
        /// </summary>
        /// <param name="log"></param>
        /// <param name="svc"></param>
        /// <returns></returns>
        public static BillSales CreateAgentPaidSalesItem(ExcelWorkLogItem log, CrmPeopleService svc)
        {
            if (log == null)
                return null;

            if (log.已收付货款 == 0)
                return null;

            if (string.IsNullOrEmpty(log.收付款人))
                throw new Exception("代收记录缺少收/付款人");

            if (AppConfig.DirectPayRecivers.Contains(log.收付款人))
                return null;

            if (svc == null)
                throw new ArgumentNullException("svc");

            BillSales item = new BillSales();
            var price_delta = 1;

            switch (log.类型)
            {
                case "进货":
                case "退货":
                case "支出":
                    item.BillType = GetBillTypeByLogType("收入");
                    price_delta = -1;
                    item.ProductName = "[代支]" + log.货物;
                    break;

                case "收入":
                case "销售":
                    item.BillType = GetBillTypeByLogType("支出");
                    item.ProductName = "[代收]" + log.货物;
                    break;

                default:
                    return null;
            }

            item.ClientName = log.收付款人;
            item.ClientId = GetClientId(item.ClientName, svc);

            item.SalesDate = log.日期;

            if (string.IsNullOrEmpty(log.单位))
            {
                item.ProductUnit = "宗";
                item.ProductAmount = 1;
                item.ProductPrice = log.已收付货款 * price_delta;

                item.Note = log.客户供应商;
                if (string.IsNullOrEmpty(log.备注) == false)
                    item.Note += ("，" + log.备注);
            }
            else if (log.IsPaidOff)
            {
                item.ProductUnit = log.单位;
                item.ProductAmount = log.数量;
                item.ProductPrice = log.单价 * price_delta;
                item.ShippingFee = log.运装卸费 * price_delta;

                item.Note = log.客户供应商;
                if (string.IsNullOrEmpty(log.备注) == false)
                    item.Note += ("," + log.备注);
            }
            else
            {
                item.ProductUnit = "宗";
                item.ProductAmount = 1;
                item.ProductPrice = log.已收付货款 * price_delta;

                item.Note = string.Format("{0}{1}{2}{3}x{4}", log.客户供应商, log.货物, log.数量, log.单位, log.单价);
                if (log.运装卸费 != 0)
                    item.Note += ("，运卸费" + log.运装卸费 + "元");
            }

            item.Status = (short)DBRecordStatus.Normal;
            item.CreatedTime = DateTime.Now;
            item.LastUpdatedTime = DateTime.Now;
            item.TotalMoney = item.ProductAmount * item.ProductPrice;
            item.IsPaidOff = false; // 代收未收回，永远都是false

            return item;
        }


        /// <summary>
        /// 根据销售日志中的记录类型来判断当前记录的单据类型
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private static short GetBillTypeByLogType(string type)
        {
            //NOTE: 进货退货按照销售进行处理
            switch (type)
            {
                case "销售":
                    return (short)BillSalesType.Sale;
                case "进货":
                    return (short)BillSalesType.Purchase;
                case "退货":
                    return (short)BillSalesType.ReturnForSale;
                case "收入":
                    return (short)BillSalesType.Income;
                case "支出":
                    return (short)BillSalesType.Pay;
                default:
                    return (short)BillSalesType.NotDefined;
            }
        }

        /// <summary>
        /// 根据货物名称来判断当前单据的类型
        /// </summary>
        /// <param name="productName"></param>
        /// <returns></returns>
        public static short GetBillTypeByProduct(string productName)
        {
            //NOTE: 进货退货按照销售进行处理
            if (string.IsNullOrEmpty(productName))
                return (short)BillSalesType.NotDefined;

            if (productName.Contains("来款"))
                return (short)BillSalesType.Income;

            if (productName.Contains("去款"))
                return (short)BillSalesType.Pay;

            if (productName.Contains("优惠"))
                return (short)BillSalesType.Discount;

            if (productName.Contains("[损失]"))
                return (short)BillSalesType.Lost;

            if (productName.Contains("[退]"))
                return (short)BillSalesType.ReturnForSale;

            if (productName.Contains("容借款"))
                return (short)BillSalesType.Income;

            if (productName.Contains("借款"))
                return (short)BillSalesType.Pay;

            if (productName.Contains("差价"))
                return (short)BillSalesType.Discount;

            if (productName.Contains("多收"))
                return (short)BillSalesType.Pay;

            if (productName.Contains("代支"))
                return (short)BillSalesType.Income;

            if (productName.Contains("代运"))
                return (short)BillSalesType.Income;

            if (productName.Contains("运费"))
                return (short)BillSalesType.Income;

            if (productName.Contains("提成"))
                return (short)BillSalesType.Discount;

            return (short)BillSalesType.Sale; // 代收/少收/销售/欠款/货款确认
        }

        public static IEnumerable<BillSales> ConvertToBillSales(IEnumerable<ExcelWorkLogItem> logs, CrmPeopleService svc, bool skipRecorded)
        {
            if (logs == null || logs.Count() == 0)
                return null;

            var salesList = new List<BillSales>();
            foreach (var item in logs)
            {
                if (skipRecorded && item.HasRecordOnBook)
                    continue;

                // 类型为销售且结清当条记录的，不需要再入账，直接计入代收
                if (item.类型 != "销售" || item.IsPaidOff == false)
                {
                    var detail_info = ClientFileService.CreateDetailSalesItem(item, svc);
                    if (detail_info != null)
                        salesList.Add(detail_info);

                    var paid_info = ClientFileService.CreatePaidSalesItem(item, svc);
                    if (paid_info != null)
                        salesList.Add(paid_info);
                }

                var agent_paid_info = ClientFileService.CreateAgentPaidSalesItem(item, svc);
                if (agent_paid_info != null)
                    salesList.Add(agent_paid_info);
            }

            return salesList;
        }

        public void Save(IEnumerable<BillSales> salesList, string sheetName)
        {
            if (salesList == null || salesList.Count() == 0)
                return;

            using (ExcelPackage package = new ExcelPackage(new FileInfo(ClientFile)))
            {
                var sheet = package.Workbook.Worksheets[sheetName];
                var rowIndex = GetFirstEmptyRowIndex(sheet, salesList.Count());

                foreach (var sale in salesList)
                {
                    //todo: maybe add a function which automically insert a new year-row when current year is different with current sale's year,not must!!!
                    var row = sheet.Cells[rowIndex, 1, rowIndex, 10];
                    FillValues(sale, row);
                    rowIndex++;
                }

                package.Save();
            }
        }

        public IEnumerable<BillSales> GetRecordList(CrmPeopleService svc, DateTime? begin = null, DateTime? end = null)
        {
            List<BillSales> result = new List<BillSales>();

            using (ExcelPackage package = new ExcelPackage(new FileInfo(ClientFile)))
            {
                var sheetList = package.Workbook.Worksheets.Where(x => x.IsDataSheet() && !x.IsHistorySheet() && !x.IsConclusionSheet());

                int clientId = DEFAULT_INVALID_CLIENT_ID;
                string clientType = null;
                var client_crm = svc.GetByName(this.ClientName);

                if (client_crm != null)
                {
                    clientId = client_crm.Id.Value;
                    clientType = client_crm.TypeCode;
                }

                #region Process

                foreach (var sheet in sheetList)
                {
                    //header row：日期    名称  数量  单位  单价  卸车  运费  金额  备注  单号
                    //year/month row
                    var header_row_first_cell = sheet.Cells[1, 1].GetValue<string>();
                    if (header_row_first_cell != "日期")
                    {
                        //SimpleLogger.Log(string.Format("info>>>{0}->{1}表不受支持", this.ClientName, sheet.Name));
                        MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Warn,
                                Title = "读取客户交易记录操作[file]",
                                Content = string.Format("{0}->{1}表不受支持", this.ClientName, sheet.Name)
                            });
                        continue;
                    }

                    bool hasBillNOColumn = !string.IsNullOrEmpty(sheet.Cells[1, 10].GetValue<string>());

                    string currentYear = null;
                    DateTime lastRowDate = SqlDateTime.MinValue.Value;
                    int lastRowDateIndex = 0;

                    for (int rowIndex = 2; rowIndex <= sheet.Dimension.Rows; rowIndex++)
                    {
                        var rowFirstCell = sheet.Cells[rowIndex, 1];
                        var rowSecondCell = sheet.Cells[rowIndex, 2];
                        var rowFirstCellValue = rowFirstCell.GetValue<string>();

                        if (rowFirstCellValue != null)
                            rowFirstCellValue = rowFirstCellValue.Trim();
                        else
                            rowFirstCellValue = string.Empty;

                        if (rowSecondCell.Merge && RegexNumber.IsMatch(rowFirstCellValue))
                            rowFirstCellValue = rowFirstCell.GetValue<DateTime>().ToString("yyyy年");


                        if (rowFirstCellValue.Contains("结清")
                            || rowFirstCellValue.Contains("来款")
                            || rowFirstCellValue.Contains("去款")
                            || rowFirstCellValue.Contains("欠款")
                            || rowFirstCellValue.Contains("小计")
                            || rowFirstCellValue.Contains("合计")
                            || rowFirstCellValue.Contains("降价"))
                            continue;

                        var rowSecondCellValue = rowSecondCell.GetValue<string>();

                        if (rowSecondCellValue != null && rowSecondCellValue.Contains("降价"))
                            continue;

                        var year_match = RegexYearCell.Match(rowFirstCellValue);
                        if (year_match.Success)
                        {
                            currentYear = year_match.Groups[1].Value;
                        }
                        else
                        {
                            if (string.IsNullOrEmpty(rowFirstCellValue))
                            {
                                if (string.IsNullOrEmpty(rowSecondCell.GetValue<string>()))
                                    continue;
                                else if (string.IsNullOrEmpty(sheet.Cells[rowIndex, 3].GetValue<string>()))
                                    continue;

                                if (rowIndex - lastRowDateIndex > 1)
                                    continue;
                            }
                            else
                            {
                                if (rowSecondCell.Merge)
                                    continue;

                                if (rowFirstCellValue == "未知")
                                {
                                    lastRowDate = lastRowDate.AddDays(1);
                                }
                                else
                                {
                                    lastRowDate = rowFirstCell.GetValue<DateTime>();

                                    if (lastRowDate == default(DateTime))
                                    {
                                        int spliteIndex = rowFirstCellValue.IndexOf("月");
                                        if (spliteIndex != -1)
                                            rowFirstCellValue = rowFirstCellValue.Substring(0, spliteIndex) + "-1";


                                        string datetimestr = string.Format("{0}-{1}", currentYear, rowFirstCellValue);

                                        if (!DateTime.TryParse(datetimestr, out lastRowDate))
                                            throw new Exception(string.Format("date parsing error:{0},{1},{2}", this.ClientName, sheet.Name, rowIndex));
                                    }
                                    else if (lastRowDate.Year.ToString() != currentYear)
                                    {
                                        lastRowDate = lastRowDate.AddYears(int.Parse(currentYear) - lastRowDate.Year);
                                    }
                                }
                            }

                            lastRowDateIndex = rowIndex;

                            #region date filter

                            if (begin.HasValue && lastRowDate < begin.Value)
                                continue;

                            if (end.HasValue && lastRowDate > end.Value)
                                continue;

                            #endregion

                            var productName = sheet.Cells[rowIndex, DataSheetColumnIndex.Product].GetValue<string>();
                            var productAmount = sheet.Cells[rowIndex, DataSheetColumnIndex.Amount].GetValue<decimal>();
                            var productUnit = sheet.Cells[rowIndex, DataSheetColumnIndex.Unit].GetValue<string>();
                            var productPrice = sheet.Cells[rowIndex, DataSheetColumnIndex.Price].GetValue<decimal>();
                            var loadingFee = sheet.Cells[rowIndex, DataSheetColumnIndex.LoadingFee].GetValue<decimal>();
                            var shippingFee = sheet.Cells[rowIndex, DataSheetColumnIndex.ShippingFee].GetValue<decimal>();
                            var total = sheet.Cells[rowIndex, DataSheetColumnIndex.Total].GetValue<decimal>();
                            var note = sheet.Cells[rowIndex, DataSheetColumnIndex.Note].GetValue<string>();

                            string billNO = null;
                            if (hasBillNOColumn)
                                billNO = sheet.Cells[rowIndex, DataSheetColumnIndex.BillNo].GetValue<string>();

                            if (clientType == "司机" || clientType == "供应商")
                            {
                                if (sheet.Name.Contains("代运费")
                                    || sheet.Name.Contains("罗取")
                                    || sheet.Name.Contains("运费"))
                                {
                                    if (productAmount > 0)
                                        productAmount *= -1;

                                    if (productPrice < 0)
                                        productPrice *= -1;

                                    if (loadingFee > 0)
                                        loadingFee *= -1;

                                    if (shippingFee > 0)
                                        shippingFee *= -1;

                                    if (total > 0)
                                        total *= -1;
                                }
                            }

                            var sales_record = new BillSales
                            {
                                BillNumber = billNO,
                                ClientId = clientId,
                                ClientName = this.ClientName,
                                CreatedTime = DateTime.Now,
                                IsPaidOff = false,
                                LastUpdatedTime = DateTime.Now,
                                LoadingFee = loadingFee,
                                Note = note,
                                ProductAmount = productAmount,
                                ProductId = 0,
                                ProductName = productName,
                                ProductPrice = productPrice,
                                ProductUnit = productUnit,
                                SalesDate = lastRowDate,
                                ShippingAddress = sheet.Name,
                                ShippingFee = shippingFee,
                                ShippingMethod = note,
                                Status = (short)DBRecordStatus.Normal,
                                TotalMoney = total,
                                BillType = GetBillTypeByProduct(productName)
                            };

                            result.Add(sales_record);
                        }
                    }
                }

                #endregion
            }

            return result;
        }
    }
}
